/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     19/02/2015 15:00:34                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GrupoTransacao') and o.name = 'FK_GRUPOTRA_REFERENCE_GRUPOUSU')
alter table GrupoTransacao
   drop constraint FK_GRUPOTRA_REFERENCE_GRUPOUSU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GrupoTransacao') and o.name = 'FK_GRUPOTRA_REFERENCE_TRANSACA')
alter table GrupoTransacao
   drop constraint FK_GRUPOTRA_REFERENCE_TRANSACA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Usuario') and o.name = 'FK_USUARIO_REFERENCE_GRUPOUSU')
alter table Usuario
   drop constraint FK_USUARIO_REFERENCE_GRUPOUSU
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GrupoTransacao')
            and   type = 'U')
   drop table GrupoTransacao
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GrupoUsuario')
            and   type = 'U')
   drop table GrupoUsuario
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Transacao')
            and   type = 'U')
   drop table Transacao
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Usuario')
            and   type = 'U')
   drop table Usuario
go

/*==============================================================*/
/* Table: GrupoTransacao                                        */
/*==============================================================*/
create table GrupoTransacao (
   CodigoGrupoUsuario   int                  not null,
   CodigoTransacao      int                  not null,
   TransacaoAcessar     varchar(1)           null default '0',
   TransacaoIncluir     varchar(1)           null default '0',
   TransacaoAlterar     varchar(1)           null default '0',
   TransacaoExcluir     varchar(1)           null default '0',
   TransacaoProcessar   varchar(1)           null default '0',
   AlteracaoUsuario     varchar(50)          not null,
   AlteracaoDataHora    datetime             not null default getdate(),
   constraint PK_GRUPOTRANSACAO primary key nonclustered (CodigoGrupoUsuario, CodigoTransacao)
)
go

/*==============================================================*/
/* Table: GrupoUsuario                                          */
/*==============================================================*/
create table GrupoUsuario (
   CodigoGrupoUsuario   int                  identity,
   NomeGrupoUsuario     varchar(50)          not null,
   AlteracaoUsuario     varchar(50)          not null,
   AlteracaoDataHora    datetime             not null default getdate(),
   constraint PK_GRUPOUSUARIO primary key nonclustered (CodigoGrupoUsuario)
)
go

/*==============================================================*/
/* Table: Transacao                                             */
/*==============================================================*/
create table Transacao (
   CodigoTransacao      int                  not null,
   SiglaSistema         varchar(5)           null,
   Descricao            varchar(50)          not null,
   LinkMenu             varchar(25)          null,
   OrdemMenu            int                  null,
   OrigemMenu           int                  null,
   ModuloMenu1          varchar(50)          null,
   ModuloMenu2          varchar(50)          null,
   ModuloMenu3          varchar(50)          null,
   ModuloMenu4          varchar(50)          null,
   ModuloMenu5          varchar(50)          null,
   TransacaoAcessar     varchar(1)           null default '0',
   TransacaoIncluir     varchar(1)           null default '0',
   TransacaoAlterar     varchar(1)           null default '0',
   TransacaoExcluir     varchar(1)           null default '0',
   TransacaoProcessar   varchar(1)           null default '0',
   AlteracaoUsuario     varchar(25)          not null,
   AlteracaoDataHora    datetime             not null default getdate(),
   constraint PK_TRANSACAO primary key nonclustered (CodigoTransacao)
)
go

/*==============================================================*/
/* Table: Usuario                                               */
/*==============================================================*/
create table Usuario (
   CodigoGrupoUsuario   int                  not null,
   LoginUsuario         varchar(50)          not null,
   NomeUsuario          varchar(50)          not null,
   Situacao             char(1)              null,
   Senha                varchar(32)          not null,
   DataCadastro         datetime             null default getdate(),
   AlteracaoUsuario     varchar(50)          not null,
   AlteracaoDataHora    datetime             not null default getdate(),
   constraint PK_USUARIO primary key nonclustered (LoginUsuario)
)
go

alter table GrupoTransacao
   add constraint FK_GRUPOTRA_REFERENCE_GRUPOUSU foreign key (CodigoGrupoUsuario)
      references GrupoUsuario (CodigoGrupoUsuario)
go

alter table GrupoTransacao
   add constraint FK_GRUPOTRA_REFERENCE_TRANSACA foreign key (CodigoTransacao)
      references Transacao (CodigoTransacao)
go

alter table Usuario
   add constraint FK_USUARIO_REFERENCE_GRUPOUSU foreign key (CodigoGrupoUsuario)
      references GrupoUsuario (CodigoGrupoUsuario)
go

